/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DATA;

import CODE.Curso;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author User
 */
public class handBD {

Connection connection = null;
Curso cur;

    public handBD () throws SQLException{
        try {
        // Load the JDBC driver
        String driverName = "org.gjt.mm.mysql.Driver"; // MySQL MM JDBC driver
        Class.forName(driverName);

        // Create a connection to the database
        String serverName = "localhost";
        String mydatabase = "academic";
        String url = "jdbc:mysql://" + serverName + "/" + mydatabase; // a JDBC url
        String username = "root";
        String password = "root";
        System.out.println(password);
            try {
                connection = (Connection) DriverManager.getConnection(url, username, password);
                System.out.println("conecto!");
            } catch (SQLException ex) {
                Logger.getLogger(accesolog.class.getName()).log(Level.SEVERE, null, ex);
            }
        } catch (ClassNotFoundException e) {
        // Could not find the database driver
        }
    }

        public String log(String login, String pas) throws SQLException {

        Statement st = null;
        ResultSet result = null;
        System.out.println(login);
        System.out.println(pas);
        String a;
        st = (Statement) connection.createStatement();
        result = st.executeQuery("select tipo from usuario where Cod_usuario ='"+login+"' and password ='"+pas+"';");
        //String a = result.getString("id");
         //System.out.println(result.getString("id"));
        if(result.next())
        {
            a = result.getString("tipo");
            System.out.println(a);
            return a;
        }
        else
            return  a = "error";
        //return result.getString("id") String ;
    }

        public ArrayList<String> obtenerDatosAlumno( String login)
        {
            ArrayList<String> datos = new ArrayList<String>();
            //Iterator<String> it = datos.iterator();
            String nombre = null;
            String apellido = null;
            String direccion = null;
            String email = null;
            String semestre = null;


        try {
            Statement st = null;
            ResultSet result = null;
            st = (Statement) connection.createStatement();
            result = st.executeQuery("select * from Alumno where Cod_alumno ='" + login + "';");
            if(result.next())
            {
                nombre = result.getString(1);
                apellido = result.getString(2);
                direccion = result.getString(3);
                email = result.getString(4);
                semestre = result.getString(5);
                datos.add(nombre);
                datos.add(apellido);
                datos.add(direccion);
                datos.add(email);
                datos.add(semestre);



            }

        } catch (SQLException ex) {
            Logger.getLogger(handBD.class.getName()).log(Level.SEVERE, null, ex);
        }
            return datos;

        }



        public ArrayList<String> Lista_cursos( )
        {
            ArrayList<String> datos = new ArrayList<String>();
            //Iterator<String> it = datos.iterator();
            String Cod_curso = null;
            String nombre_curso = null;
            String objetivo_curso = null;
            Integer horas_tecnicas  = null;
            Integer horas_practicas = null;
            Integer nota = null;
            String plancurricular = null;


        try {
            Statement st = null;
            ResultSet result = null;
            st = (Statement) connection.createStatement();
            result = st.executeQuery("select * from curso;");
            if(result.next())
            {
                Cod_curso = result.getString(1);
                nombre_curso = result.getString(2);
                objetivo_curso = result.getString(3);
                horas_tecnicas = result.getInt(4);
                horas_practicas = result.getInt(5);
                plancurricular = result.getString(10);
                nota = result.getInt(11);
                datos.add(Cod_curso);
                datos.add(nombre_curso);
                datos.add(objetivo_curso);
                datos.add(String.valueOf(horas_tecnicas));
                datos.add(String.valueOf(horas_practicas));
                datos.add(plancurricular);
                datos.add(String.valueOf(nota));
            }

        } catch (SQLException ex) {
            Logger.getLogger(handBD.class.getName()).log(Level.SEVERE, null, ex);
        }
            return datos;

        }


   public ArrayList<String> getCurso()
    {
        ArrayList<String> curso = new ArrayList<String>();
        Statement st = null;
        ResultSet result = null;
        cur = new Curso();
        try {
            st = (Statement) connection.createStatement();
            result = st.executeQuery("select `nombre_curso` from `curso`;");
            while (result.next()) {
                String a = result.getString("nombre_curso");
                //cur.setCod(a);
                cur.setNombre(a);
                curso.add(a);
            }

        } catch (SQLException ex) {
            Logger.getLogger(accesolog.class.getName()).log(Level.SEVERE, null, ex);
        }
        return curso;
    }





        public ArrayList<String>ObtenerDatos(String perfil, String cod)
        {
            ArrayList<String> datos = new ArrayList<String>();
            //Iterator<String> it = datos.iterator();
            String codigo = null;
            String nombre = null;
            String apellido = null;
            String direccion = null;
            String email = null;
            String telefono = null;


        try {
            Statement st = null;
            ResultSet result = null;
            st = (Statement) connection.createStatement();
            result = st.executeQuery("select * from alumno where Cod_alumno ='" + cod + "';");
            if(result.next())
            {
                codigo = result.getString(1);
                nombre = result.getString(2);
                apellido = result.getString(3);
                direccion = result.getString(7);
                email = result.getString(5);
                telefono = result.getString(6);
                datos.add(codigo);
                datos.add(nombre);
                datos.add(apellido);
                datos.add(direccion);
                datos.add(email);
                datos.add(telefono);



            }

        } catch (SQLException ex) {
            Logger.getLogger(handBD.class.getName()).log(Level.SEVERE, null, ex);
        }
            return datos;

        }

    public void Guardar_Datos(ArrayList<String> datos_panel,String perfil) {
        try {
            Statement sentencia = null;
            sentencia = (Statement) connection.createStatement();
            System.out.println("updetea");

            String cod = datos_panel.get(0);
            String direccion = datos_panel.get(1);
            String email = datos_panel.get(2);
            String telefono = datos_panel.get(3);
            String comando = "UPDATE " + perfil + " SET direccion_" + perfil + " ='" + direccion + "', email_" + perfil + " = '" + email + "', telefono_" + perfil + " = '" + telefono + "' WHERE Cod_" + perfil + "= '" + cod + "';";
            sentencia.executeUpdate(comando);
            sentencia.close();

        } catch (SQLException ex) {
            Logger.getLogger(handBD.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public ArrayList<ArrayList<String>> ObtenerAvance(String cod) {
        ArrayList<ArrayList<String>> g =  new ArrayList<ArrayList<String>>();
        return g;
/*
        initComponents();
            javax.swing.table.DefaultTableModel t = new javax.swing.table.DefaultTableModel();
            jTable1.setModel(t);
            t.addColumn((Object) "Sem.");
            t.addColumn((Object) "Nombre del Curso");
            t.addColumn((Object) "Tipo");
            t.addColumn((Object) "Créditos");
            t.addColumn((Object) "Nota Final");
            t.addColumn((Object) "Aprobó");
            t.addColumn((Object) "Periodo");*/


    }


   public static void main(String args[]) {
       handBD db;
        try {
            db = new handBD();
            ArrayList<String> curso = db.getCurso();
            for (int i = 0; i < curso.size(); i++) {
                System.out.println(curso.get(i));

            }
        } catch (SQLException ex) {
            Logger.getLogger(handBD.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

   public void guardarmatricula(String curso) throws SQLException {
        Statement sentencia = null;
        try {
            sentencia = (Statement) connection.createStatement();
            String comando = "INSERT INTO matriculado(curso)" +
                    " VALUES ('" + curso + "')";
            sentencia.executeUpdate(comando);

        } finally {

        sentencia.close();
      }

    }



}
